/*
 * Created on 2004/12/23
 *
 * This class is used to construct a SQL query and executes it
 *
 */
package com.erp.app.common.query;

import java.lang.reflect.Constructor;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Set;

import com.erp.framework.model.dao.ServiceLocator;
import com.erp.framework.util.CommonUtil;

public class DynamicQuery {
    private HashMap columns = new HashMap();
    private HashMap tables = new HashMap();
    private String whereStmt = "";
    private String orderStmt = "";
    private boolean useReflection;
    private boolean isDistinct = false;

    /**
     * 
     * @return A collection of result from database in HashMap based on
     *         user-defined query. Users of this class have to map the values
     *         retrieved from HashMap to whatever data structure they want to
     *         use. This method always returns a non-null collection.
     * @throws Exception
     */
    public Collection execute() throws Exception {
        ServiceLocator service = ServiceLocator.getInstance();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();
        Set set = columns.keySet();
        Object[] objs = set.toArray();
        String columnName = "";
        Class clazz = null;
        Constructor truct = null;
        ResultSetMetaData rsmd = null;

        try {
            conn = service.getDSDBConnection();
            conn.setReadOnly(true);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(createQuery());

            int sql_type = 0;

            while (rs.next()) {
                rsmd = rs.getMetaData();
                HashMap result = new HashMap();
                for (int i = 0; i < objs.length; i++) {
                    Object obj = null;
                    columnName = (String) objs[i];
                    // ArrayList types = (ArrayList)columns.get(objs[i]);
                    DynamicQueryColumn dqc = (DynamicQueryColumn) columns.get(columnName);
                    columnName = CommonUtil.isEmpty(dqc.getAlias()) ? columnName : dqc.getAlias(); // Alias
                                                                                                    // takes
                                                                                                    // precedent
                                                                                                    // over
                                                                                                    // column
                                                                                                    // name

                    if (useReflection) {
                        clazz = (Class) dqc.getReflectionClass();
                        truct = clazz.getConstructor(new Class[] { String.class });
                    }

                    sql_type = rsmd.getColumnType(i + 1);

                    if (sql_type == java.sql.Types.VARCHAR) {
                        String temp = rs.getString(columnName);
                        if (useReflection)
                            obj = temp == null ? null : truct.newInstance(new Object[] { temp.toString() });
                        else
                            obj = temp;
                    } else if (sql_type == java.sql.Types.NUMERIC) {
                        BigDecimal temp = rs.getBigDecimal(columnName);
                        if (useReflection)
                            obj = temp == null ? null : truct.newInstance(new Object[] { temp.toString() });
                        else
                            obj = temp;
                    } else if (sql_type == java.sql.Types.DATE) {
                        Date temp = rs.getDate(columnName);
                        if (useReflection)
                            obj = temp == null ? null : truct.newInstance(new Object[] { temp.toString() });
                        else
                            obj = temp;
                    }
                    result.put(CommonUtil.isEmpty(dqc.getAlias()) ? columnName : dqc.getAlias(), obj);
                }
                list.add(result);
            }

        } catch (SQLException e) {
            throw e;
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
                throw e;
            }
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException e) {
                throw e;
            }
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                throw e;
            }
        }

        return list;
    }

    /**
     * This insert a table column which we want to retrieve from database. This
     * assumes that no two column names are the same.
     * 
     * @param name
     *            This specifies the column name
     * @param fieldType
     *            This specifies the object type in which we store the data
     * @param sqlType
     *            This specifies the field type in database
     * @throws IllegalArgumentException
     */
    public void addColumn(DynamicQueryColumn column) {
        columns.put(column.getColumnName(), column);
    }

    /*
     * public void addColumn(String name, Class fieldType, int sqlType) throws
     * IllegalArgumentException { if (columns.containsKey(name)) throw new
     * IllegalArgumentException("Duplicate column name");
     * 
     * ArrayList types = new ArrayList(2); types.add(fieldType); types.add(new
     * Integer(sqlType)); columns.put(name, types); }
     */
    public void addTable(String name) throws IllegalArgumentException {
        addTable(name, "");
    }

    public void addTable(String name, String identifier) throws IllegalArgumentException {
        if (tables.containsKey(name))
            throw new IllegalArgumentException("Duplicate table name");

        tables.put(name, identifier);
    }

    private String createQuery() {
        StringBuffer sb = new StringBuffer();
        sb.append(createColumnStatement());
        sb.append(createFromStatement());
        sb.append(whereStmt.length() > 0 ? whereStmt.indexOf("WHERE") == -1 ? " WHERE " : "" : "");
        sb.append(whereStmt);
        sb.append(orderStmt.length() > 0 ? " ORDER BY " : "");
        sb.append(orderStmt);

        return sb.toString();
    }

    private String createColumnStatement() {
        DynamicQueryColumn dbc = null;
        StringBuffer sb = new StringBuffer("SELECT");
        if (isDistinct)
            sb.append(" DISTINCT");
        Set set = columns.keySet();
        Object[] objs = set.toArray();

        for (int i = 0; i < objs.length; i++) {
            dbc = (DynamicQueryColumn) columns.get(objs[i]);
            sb.append(i > 0 ? " ," : " ");
            sb.append((String) objs[i]);
            if (!CommonUtil.isEmpty(dbc.getAlias())) {
                sb.append(" ");
                sb.append(dbc.getAlias());
            }
        }

        return sb.toString();
    }

    private String createFromStatement() {
        StringBuffer sb = new StringBuffer(" FROM");
        Set set = tables.keySet();
        Object[] objs = set.toArray();

        for (int i = 0; i < objs.length; i++) {
            sb.append(i > 0 ? " ," : " ");
            sb.append((String) objs[i]);
            sb.append(" ");
            sb.append((String) tables.get(objs[i]));
        }

        return sb.toString();
    }

    private String createWhereStatement() {
        StringBuffer sb = new StringBuffer();

        return sb.toString();
    }

    private String createOrderByStatement() {
        StringBuffer sb = new StringBuffer();

        return sb.toString();
    }

    public void setWhereStatement(String whereStmt) {
        this.whereStmt = whereStmt;
    }

    public void setOrderStatement(String orderStmt) {
        this.orderStmt = orderStmt;
    }

    public void setUseReflection(boolean useReflection) {
        this.useReflection = useReflection;
    }

    public void setIsDistinct(boolean isDistinct) {
        this.isDistinct = isDistinct;
    }

    public String toString() {
        return createQuery();
    }
}
